using System;
using System.Data;
using System.Text;

using Elev.Core.Data;

namespace Elev.Core.DAL
{
    internal class ExamTypeInternal : SqliteTable
    {
        /// <summary>
        /// Returns a DataTable containing all the exam types in the database
        /// </summary>
        internal DataTable GetExamTypes()
        {
            string SQL = "SELECT * FROM ExamType";
            return SqliteAdapter.Get().Execute(SQL);
        }

        /// <summary>
        /// Returns a DataTable containing one given exam type
        /// </summary>
        internal DataTable GetExamType(int ID)
        {
            string SQL = "SELECT * FROM ExamType WHERE ID = @ID";
            return SqliteAdapter.Get().Execute(SQL, "@ID", ID);
        }

        internal int Add(string Name, int Coefficient, int DisplayColor)
        {
            if (Name == "")
                throw new ArgumentException("An exam type name is required.");

            string SQL = "INSERT INTO ExamType(Name, Coefficient, DisplayColor) VALUES(@Name, @Coefficient, @DisplayColor)";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Name", Name, "@Coefficient", Coefficient, "@DisplayColor", DisplayColor);

            return Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar("SELECT MAX(ID) FROM ExamType")); 
        }

        internal void Update(int ID, string Name, int Coefficient, int DisplayColor)
        {
            if (Name == "")
                throw new ArgumentException("An exam type name is required.");

            string SQL = "UPDATE ExamType SET Name = @Name, Coefficient = @Coefficient, DisplayColor = @DisplayColor WHERE ID = @ID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Name", Name, "@Coefficient", Coefficient, "@DisplayColor", DisplayColor, "@ID", ID);
        }

        internal void Delete(int ID)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append("DELETE FROM Grade WHERE ExamID in (SELECT ID FROM Exam WHERE ExamTypeID = @ExamTypeID)");
            SqliteAdapter.Get().ExecuteNonQuery(sql.ToString(), "@ExamTypeID", ID);

            sql = new StringBuilder();
            sql.Append("DELETE FROM Exam WHERE ExamTypeID = @ExamTypeID");
            SqliteAdapter.Get().ExecuteNonQuery(sql.ToString(), "@ExamTypeID", ID);

            sql = new StringBuilder();
            sql.Append("DELETE FROM ExamType WHERE ID = @ID");
            SqliteAdapter.Get().ExecuteNonQuery(sql.ToString(), "@ID", ID);
        }

        #region SqliteTable Members

        public override void Create()
        {
            string SQL = "CREATE TABLE [ExamType] (" +
                            " ID INTEGER DEFAULT '' NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE," +
                            " Name VARCHAR(50) DEFAULT '' NOT NULL," + 
                            " Coefficient INTEGER DEFAULT '1' NOT NULL," +
                            " DisplayColor INTEGER DEFAULT '0' NOT NULL)";

            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        public override void Drop()
        {
            string SQL = "DROP TABLE [ExamType]";
            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        #endregion
    }
}
